﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;

namespace OverStore.Sql
{
    /// <summary>
    /// Contains set of parameters required for 
    /// builing SQL commands using TableSchema
    /// </summary>
    public abstract class SqlFormatting
    {
        /// <summary>
        /// Default map of conversion Type to corresponding <see cref="DbType"/>.
        /// </summary>
        public static readonly IDictionary<Type, DbType> DefaultConversionTable = new Dictionary<Type, DbType>
        {
            { typeof(byte[]), DbType.Binary },
            { typeof(byte), DbType.Byte },
            { typeof(char), DbType.String },
            { typeof(bool), DbType.Boolean },
            { typeof(DateTime), DbType.DateTime },
            { typeof(decimal), DbType.Decimal },
            { typeof(double), DbType.Double },
            { typeof(Guid), DbType.Guid },
            { typeof(short), DbType.Int16 },
            { typeof(int), DbType.Int32 },
            { typeof(long), DbType.Int64 },
            { typeof(sbyte), DbType.SByte },
            { typeof(float), DbType.Single },
            { typeof(string), DbType.String },
            { typeof(ushort), DbType.UInt16 },
            { typeof(uint), DbType.UInt32 },
            { typeof(ulong), DbType.UInt64 }
        };

        /// <summary>
        /// Gets a value indicating whether specified ADO.NET provider supports few SQL instructions in text of db command.
        /// </summary>
        public virtual bool SupportsCompositeCommands { get { return true; } }

        /// <summary>
        /// Gets a value indicating whether specified database supports schemas.
        /// </summary>
        public virtual bool SupportSqlSchemas { get { return true; } }

        /// <summary>
        /// Gets a value indicating whether specified database supports autogenerated identifiers.
        /// </summary>
        public abstract bool SupportsIdentifierGeneration { get; }

        /// <summary>
        /// Gets a value indicating whether ADO.NET provider supports named parameters.
        /// </summary>
        public virtual bool SupportsNamedParameters { get { return true; } }

        /// <summary>
        /// Generates SELECT command returns last inserted identifier for the specified table.
        /// </summary>
        public abstract string LastIdentifierOfTable(string tableName, string identifierAlias);

        /// <summary>
        /// Formats reference to specified table of specified schema. If schema parameter is null or empty,
        /// method formats reference to table without a schema.
        /// </summary>
        public abstract string Table(string schema, string tableName);

        /// <summary>
        /// Formats reference to column of specified table or table alias.
        /// </summary>
        public abstract string Column(string formattedTableOrAlias, string column);

        /// <summary>
        /// Formats reference to specified column without table or alias.
        /// </summary>
        public abstract string Column(string column);

        /// <summary>
        /// Formats the parameter with specified name. 
        /// If SqlFormatting options does not support named parameter, method must return parameter placeholder.
        /// </summary>
        public abstract string Parameter(string parameterName);

        /// <summary>
        /// Gets <see cref="DbType"/> corresponding to specified <see cref="Type"/> for current database.
        /// </summary>
        public virtual DbType ToDatabaseType(Type type)
        {
            if (type == null)
                throw new ArgumentNullException("type");
            var actualType = type;
            if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))
                actualType = type.GetGenericArguments()[0];
            if (actualType.IsEnum)
                actualType = Enum.GetUnderlyingType(actualType);
            DbType result;
            if (DefaultConversionTable.TryGetValue(actualType, out result))
                return result;
            throw new ArgumentException(
                        String.Format(
                            CultureInfo.InvariantCulture, 
                            "Type {0} has no corresponding DbType.", 
                            actualType));
        }
    }

    /// <summary>
    /// Formatting options for MS SQL Server.
    /// </summary>
    public class SqlServerFormatting : SqlFormatting
    {
        /// <summary>
        /// Formats reference to specified table of specified schema. If schema parameter is null or empty,
        /// method formats reference to table without a schema.
        /// </summary>
        /// <param name="schema"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public override string Table(string schema, string tableName)
        {
            if (String.IsNullOrEmpty(tableName))
                throw new ArgumentNullException("tableName");
            if (String.IsNullOrEmpty(schema))
                return String.Format("[{0}]", tableName);
            else
                return String.Format("[{0}].[{1}]", schema, tableName);
        }

        /// <summary>
        /// Formats reference to column of specified table or table alias.
        /// </summary>
        public override string Column(string formattedTableOrAlias, string column)
        {
            if (String.IsNullOrEmpty(formattedTableOrAlias))
                throw new ArgumentNullException("formattedTableOrAlias");
            if (String.IsNullOrEmpty(column))
                throw new ArgumentNullException("column");
            return String.Format("{0}.[{1}]", formattedTableOrAlias, column);
        }

        /// <summary>
        /// Formats reference to specified column without table or alias.
        /// </summary>
        public override string Column(string column)
        {
            if (String.IsNullOrEmpty(column))
                throw new ArgumentNullException("column");
            return String.Format("[{0}]", column);
        }

        /// <summary>
        /// Formats the parameter with specified name.
        /// If SqlFormatting options does not support named parameter, method must return parameter placeholder.
        /// </summary>
        public override string Parameter(string parameterName)
        {
            if (String.IsNullOrEmpty(parameterName))
                throw new ArgumentNullException("parameterName");
            return "@" + parameterName;
        }

        /// <summary>
        /// Gets a value indicating whether specified ADO.NET provider supports few SQL instructions in text of db command.
        /// </summary>
        /// <value></value>
        public override bool SupportsCompositeCommands { get { return true; } }

        /// <summary>
        /// Gets a value indicating whether specified database supports schemas.
        /// </summary>
        /// <value></value>
        public override bool SupportSqlSchemas { get { return true; } }

        /// <summary>
        /// Gets a value indicating whether specified database supports autogenerated identifiers.
        /// </summary>
        public override bool SupportsIdentifierGeneration { get { return true; } }

        /// <summary>
        /// Generates SELECT command returns last inserted identifier for the specified table.
        /// </summary>
        public override string LastIdentifierOfTable(string tableName, string identifierAlias)
        {
            if (String.IsNullOrEmpty(tableName))
                throw new ArgumentNullException("tableName");
            if (String.IsNullOrEmpty(identifierAlias))
                throw new ArgumentNullException("identifierAlias");
            return String.Format("SELECT @@Identity AS {0}", identifierAlias);
        }
    }

    /// <summary>
    /// Formatting options for SQL Server Compact.
    /// </summary>
    public class SqlServerCeFormatting : SqlFormatting
    {
        /// <summary>
        /// Formats reference to specified table of specified schema. If schema parameter is null or empty,
        /// method formats reference to table without a schema.
        /// </summary>
        public override string Table(string schema, string tableName)
        {
            return String.Format("[{0}]", tableName);
        }

        /// <summary>
        /// Formats reference to column of specified table or table alias.
        /// </summary>
        public override string Column(string formattedTableOrAlias, string column)
        {
            if (String.IsNullOrEmpty(formattedTableOrAlias))
                throw new ArgumentNullException("formattedTableOrAlias");
            if (String.IsNullOrEmpty(column))
                throw new ArgumentNullException("column");
            return String.Format("{0}.[{1}]", formattedTableOrAlias, column);
        }

        /// <summary>
        /// Formats reference to specified column without table or alias.
        /// </summary>
        public override string Column(string column)
        {
            if (String.IsNullOrEmpty(column))
                throw new ArgumentNullException("column");
            return String.Format("[{0}]", column);
        }

        /// <summary>
        /// Formats the parameter with specified name.
        /// If SqlFormatting options does not support named parameter, method must return parameter placeholder.
        /// </summary>
        public override string Parameter(string parameterName)
        {
            if (String.IsNullOrEmpty(parameterName))
                throw new ArgumentNullException("parameterName");
            return "@" + parameterName;
        }

        /// <summary>
        /// Gets a value indicating whether specified ADO.NET provider supports few SQL instructions in text of db command.
        /// </summary>
        /// <value></value>
        public override bool SupportsCompositeCommands { get { return false; } }

        /// <summary>
        /// Gets a value indicating whether specified database supports schemas.
        /// </summary>
        /// <value></value>
        public override bool SupportSqlSchemas { get { return false; } }

        /// <summary>
        /// Gets a value indicating whether specified database supports autogenerated identifiers.
        /// </summary>
        public override bool SupportsIdentifierGeneration { get { return true; } }

        /// <summary>
        /// Generates SELECT command returns last inserted identifier for the specified table.
        /// </summary>
        public override string LastIdentifierOfTable(string tableName, string identifierAlias)
        {
            if (String.IsNullOrEmpty(tableName))
                throw new ArgumentNullException("tableName");
            if (String.IsNullOrEmpty(identifierAlias))
                throw new ArgumentNullException("identifierAlias");

            return String.Format("SELECT @@Identity AS {0}", identifierAlias);
        }
    }
}